I everyone in this lecture will discuss the data analytics options available in excel.
We will be covering the use of parts of whatever analysis called scenario manager and.
Then we will all the maximization problem using the Simplex linear programming method.
After that we will do a regression analysis.
Please note I will not go over the technique itself.
That is I will not discuss how simplex method works or how regression works.
That will be part of another series of lectures.
For now we will only look at how to apply these analysis.
First of all in this simple example I have a -- job selling two types of milkshakes magazines and
chocolate chip.
Imagine I have limited quantity of milk available with me with which I can make only a hundred glasses
of shakes.
Now if a glass of mango sells for 50 and a glass of chocolate sells for 30.
How many glasses of mango shake Should I sell to get a revenue of forty five hundred.
Let's prepare the sheet for this analysis first.
Very well.
I don't want the changes.
What is the percentage of milk.
I will be using for magazines for that I have kept the sale and my data diapers percentage
in the next sale.
I multiply this percentage with the total milk available.
This will give me the number of mango shakes and selling.
Next is one minus the same thing which will give me number of glasses of chocolate saying that I am
selling and then I multiplying these two and adding up with the price of these so as to get the total
earnings right now.
If the percentage is zero all I'm selling is Dr. check and every glass of chocolate takes us what 30.
So it is coming out to be three thousand.
Now if I change the zero percent to 20 out of hundred glasses I am selling glasses of mango.
We just told him and 80 glasses of chocolate check which will come out to the ending of 24 under or
a little met or learning off party 400.
But what I want to calculate is what should be this percentage ever want revenue of forty five hundred
and then just keep on doing this.
It didn't really matter to the answer or Excel provides me with the option of what is goal take analysis.
So let's go to Data go to whatever analysis in this select.
You see three options.
One is to say this say which will be the thirty four hundred value right now.
To value forty five hundred.
Which is my goal.
By changing say the percent data that I have and look on.
Okay.
You can see that they don't value was done by Excel and it has reached a solution of 75 percent.
So if you say 75 percent of glasses as managing and 25 glasses of chocolate cake and you get an earnings
of 40.
I wonder if you do not have a particular goal in mind.
You can see different values of your earnings for different values of your percentage of money being
used for different checks that can be done using the what if analysis.
Scenario manager option you go to or difficulties click on scenario manager you add these scenarios
it so click on add you can give a any name to the scenario you can give a name of 10 changing say its
9.
Okay
point 1 0.
0.
We lag more scenarios to this which will be 30 percent
point of view.
You can add 50 by saying
point 5 you do it added.
70 percent.
Point 7.
Odd 90 percent.
Okay.
You can see these are the five scenarios that we are creating for different percentages of 10 30 50
70 and 90.
Let us see what will be earnings.
Now you click on somebody trying turn say is it for Intel and now when you click on OK you can see data
scenario somebody with us right now.
And there's somebody you can see for 10 percent.
The earnings are coming out with thirty two hundred but 30 percent they only got 36 under and so on
for 90 percent and we have an earnings of 40 and under current value which we guard with the goal take
was 14.
I wonder at 75 percent.
So definitely worth of analysis in which we saw four different scenarios.
What will be a isn't this a logical scenario management next let us solve the maximization problem using
this emplacement here.
These are the three types of sheets really I want to identify the quantity of each thing that I should
produce to maximize my profit.
I have the below information.
Each manual check gives me a profit margin of 50 banana she gives me a profit margin of thirty and totally
gives me a profit margin not forty let.
But at last cost of a mango shake is 40 of bananas you get 20 and chocolate chip gets 50 and distorted
space in some units for a glass of mango shake I need for you in self storage space lots of bananas
it requires six votes of storage space and a glass of chocolate it requires only two units of storage
space these are two constraints available with me the total expenditure on only three shakes should
be less than thousand which is disgusting and need total space taken should be less than 60 it is my
space budget so it total expenditure will actually be equal to the quantity of mango -- multiplied
by the cost of my English plus or not I shape quantity like my cost of banana shake chocolate chip quantity
multiplied by cost of chocolate chip.
This is not a non because quantity of black elephant.
Similarly if I space taken this multiply the storage space off by Glass mango she blushes or banana
she went multiply by bloodlust or its space requirement of banana shade chocolate shade glasses multiplied
by space for chocolate chip.
Both of these are zero right now and they should be less than the respective constraint and the net
profit will be equal to number of glasses of man pushing into profit margin plus plus number of glasses
of bananas shake and do profit margin less no less trucker and do not have chocolate.
Is it all digital.
I want to maximize this net profit so before using these solver we have to add that an option head.
So I let it go.
No not visible here.
Some of you may be able to seed if you have already I did that.
So what I did.
We will go and add it first.
Go to find options.
There is this option of added
click go select the solver at him and click on.
Okay.
You can see the silhouette I didn't know I did in the data menu.
Click on solver the first thing.
It asked Felicity set objective.
What do you want to achieve.
So I want to get this value and I want to maximize it.
This is selected by changing variables cells.
These three are my variable cells
and what are the constraints.
So I have this no less than equal to this number.
AG And this numbered less than equal to space.
Okay.
So you can see that these two constraints are added.
One is for the total monetary budget and the other is for the space budget.
Now we will select the Simplex linear programming method and click on solid click Okay.
If you want any particular report of uncertain sensitivity or limits if you understand this you can
also select these.
Do not want these.
Do not let this look on.
Okay.
And you can see that it is giving me a solution that I should say eight point three three glasses of
mango shape and 39 3D glasses of chocolaty.
And this is the maximum profit under these constraints that I can get for our shop.
So 950 is the maximum profit that I can get out of these constraints.
Next we will look at how to do a regression analysis.
This will be a linear regression problem.
The data that I have is for that chip shop.
I have two types of expenditure.
One is the marketing expenditure the advertisements or the mailing or other types of marketing activity
that I am doing to promote my shake shop.
And the that is the expenditure on quality control.
Probably if I have a better quality of shakes probably I'll have more revenue.
So these are the two types of expenditures that I'm doing and I'm looking at the overall sales revenue
that I'm getting or different months.
I want to identify which of these two are actually related to the final sales revenue and which of these
two will give me more change on the sales revenue so that I should focus more on my marketing expenditure
on more on my quality control expenditure to do this.
We need an added analysis tool that will go to find options and go analysis tool back click on.
Okay.
You can see that there is an analysis to back.
I did it click on this.
The various analysis methods that you can see here.
We'll be doing a regression only made no use regression and click on OK input right Ange will be our
sales revenue which is the dependent variable which may be dependent on the marketing expenditure and
quality control expenditure for the X range.
We have the independent variables which as the marketing expenditure and the quality control expenditure
has labels and one output and new worksheet everything in it is okay click on.
Okay.
You can see that regression output is given in a new sheet listing you can see is the r value R gives
just the goodness of fit.
So these two expenditures are actually giving us a very good fit.
That is very less variation out of this model which this model is not able to explain an Oscar rally
of 1 8 5 is actually giving us a very good track.
Next thing to observe is the significance f value.
It could be less than 1 0 5 to have a significant rally of more than 95 percent.
Next on the coefficient This is one of the variables marketing expenditure.
It has a coefficient of one point two quality control expenditure has a copy of one point seven and
there is an intercept of those fifty eight.
So if you might have this with one point to add this value into one point seven and then add this coefficient.
This will give a deep predictive value for this year's revenue not let us copy this go fishing and pasted
it is a marketing commission.
This is the quality control completion.
This one is the sea so the predicted value by our linear regression model goes like this is equal to
this.
Do this this this.
Laughs This
you can see that the predicted value is actually very close to the sales revenue before extending it.
Remember to use dollar signs
extended to the bottom you can see that most of our times it is very close to the actual value.
And in the next month if you want to predict what will be your sales revenue given that you are spending
500 on marketing expenditure and then on quality control you know your sales revenue should be somewhere
around 20 500.
Another thing that you should note is coefficient for quality control is one point seven and coefficient
for marketing expenditure is one point two.
So there is more sensitivity offset the revenue towards quality control expenditure.
So if you have a budget constraint probably you should invest more on quality control instead of marketing
which will give you more of a new India.
That's all you make of it in the data analysis lectures.
Next we will look at these macros option available in Excel.
Thanks.